package com.siqi.hr.system.custom.db.builder;

import com.siqi.hr.system.common.authority.Sysdata;
import com.siqi.hr.system.common.utils.CollectionUtils;
import com.siqi.hr.system.custom.db.builder.intf.DbCreater;
import com.siqi.hr.system.custom.entity.EntityRecord;
import com.siqi.hr.system.custom.entity.FieldRecord;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedHashSet;
import java.util.List;

/**
 * 创建表、事务回滚dao接口实现类
 * @data      2016-08-30
 */
public abstract class DbTableCreater implements DbCreater {
    //操作数据库模板
    protected JdbcTemplate jdbcTemplate;
    //实体对象
    protected EntityRecord entityRecord;
    //存放字段Set集合
    protected LinkedHashSet<FieldRecord> fields = new LinkedHashSet<FieldRecord>();
    //存放主外键Set集合
    protected LinkedHashSet<String> keys = new LinkedHashSet<String>();
    //定义创建表编码格式
    protected String tableOPtions = "ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin";
    //创建实体表时是否创建名称字段
    private boolean notName = false;

    /**
     * 获取实体表中最大的tableCode
     * @author       xiaogq
     * @return
     */
    private int getNextTableCode(){
        return jdbcTemplate.queryForObject( " select max(table_code) from sys_custom_entity",Integer.class ) + 1;
    }

    // 可对默认的执行语句进行更改
    public abstract void preparationCreateStal();

    public DbTableCreater(){
        super();
    }
    public DbTableCreater(EntityRecord entityRecord) {
        super();
        this.entityRecord = entityRecord;
    }

    public DbTableCreater(EntityRecord entityRecord, boolean notName) {
        super();
        this.notName = notName;
        this.entityRecord = entityRecord;
    }

    /**
     * 创建自定义实体
     */
    @Override
    public int create(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
        final int tableCode = getNextTableCode();
        entityRecord.setTableCode(tableCode);
        //创建主实体表
        int lens = createTable();
        if(lens > 0){
            //添加到实体信息表和字段信息表中
            addRecords();
        }
        return lens;
    }

    /**
     * 创建表方法
     * @author    xiaogq
     */
    public int createTable(){
        //创建自定义实体时初始化Id、name字段
        int [] lens = new int[0];
        initTableProperty();
        preparationCreateStal();
        String batchSql = "";
        List<String> sqlList = new ArrayList<String>();
        for( FieldRecord field : fields ){
            if( field.parse().isQuote() ){
                if(field.getName().split("_")[1].equals("t")){
                    if(field.getName().startsWith("pk_t_2")){
                        field.setColomuDefines( " bigint(20) " );
                    }else{
                        field.setColomuDefines( " varchar(20) " );
                    }
                }else{
                    field.setColomuDefines( " int(11) " );
                }
                //如果是引用字段,创建表时添加外键
                keys.add( " CONSTRAINT " + field.foreignKeyDefinition() );
            }
        }
        String sql = "SELECT DISTINCT table_name from information_schema.columns where table_name like 'z\\_"+ entityRecord.getTableCode()+"\\_%'";
        List<String> quoteList = null ;
        try {
            quoteList = jdbcTemplate.queryForList(sql, String.class);
            if(quoteList!= null){
                for (String quoteStr : quoteList) {
                    batchSql = "drop table if exists " + quoteStr;
                    sqlList.add(batchSql);
                }
            }
        } catch (DataAccessException e) {
            e.printStackTrace();
        }

        //注释人：xiaogq 如果删除表的id是其他表的外键，此表无法删除
        try {
            batchSql = "call DELETE_FOREIGN_TTABLE('"+ Sysdata.getInstance().getDatabase()+"', 't_"+entityRecord.getTableCode()+"')";
            sqlList.add(batchSql);
        } catch (DataAccessException e) {
            e.printStackTrace();
        }
        batchSql = "drop table if exists t_"+ entityRecord.getTableCode();
        sqlList.add(batchSql);

        StringBuilder sb = new StringBuilder("create table if not exists ").append( "t_" + entityRecord.getTableCode());
        sb.append("(").append( CollectionUtils.join( fields )).append(",").append( CollectionUtils.join(keys) ).append(")");
        sb.append(tableOPtions);
        sqlList.add(sb.toString());

        if(sqlList != null && sqlList.size() > 0){
            lens = jdbcTemplate.batchUpdate(sqlList.toArray(new String [sqlList.size()]));
        }
        return lens.length;
    }

    /**
     * 创建自定义实体时初始化Id、name字段
     * @author     xiaogq
     */
    public void initTableProperty(){
        FieldRecord fieldRecord = new FieldRecord();
        fieldRecord.setEntityRecord(entityRecord);
        fieldRecord.setName("id");
        fieldRecord.setInchinese("id");
        fieldRecord.setLength(20);
        fieldRecord.setSysField(true);
        fields.add(fieldRecord);
        if(!notName){
            FieldRecord fieldRecord2 = new FieldRecord();
            fieldRecord2.setEntityRecord(entityRecord);
            fieldRecord2.setName("name_varchar");
            fieldRecord2.setInchinese("名称");
            fieldRecord2.setSysField(true);
            fieldRecord2.setNulldisable(true);
            fieldRecord2.setLength(64);
            fields.add(fieldRecord2);
        }
        keys.add( "primary key (id)" );
    }


    // 将记录添加到实体信息表和字段信息表中
    public void addRecords(){
        // 添加表记录
        StringBuffer sql = new StringBuffer("insert into sys_custom_entity(name,inchinese,table_code,create_date,update_date,");
        sql.append(" belong_mudule,permission,system_entity,define_type,define_field,define_approval,");
        sql.append(" define_stage_manager,can_quote,use_recyclebin,define_set,set_element,");
        sql.append(" nodisplay) value(?,?,?,now(),now(),?,?,?,?,?,?,?,?,?,?,?,?)");
        jdbcTemplate.update(sql.toString(), new PreparedStatementSetter(){
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setString(1, entityRecord.getName());
                ps.setString(2, entityRecord.getInchinese());
                ps.setInt(3, entityRecord.getTableCode());
                ps.setString(4, entityRecord.getBelongMudule());
                ps.setBoolean(5, entityRecord.getPermission());
                ps.setBoolean(6, false);
                ps.setBoolean(7,entityRecord.getDefineType()==null ? false:entityRecord.getDefineType());
                ps.setBoolean(8,entityRecord.getDefineField()==null ? false:entityRecord.getDefineField());
                ps.setBoolean(9,entityRecord.getDefineApproval()==null ? false:entityRecord.getDefineApproval());
                ps.setBoolean(10,entityRecord.getDefineStageManager()==null ? false:entityRecord.getDefineStageManager());
                ps.setBoolean(11,entityRecord.getCanQuote()==null ? false:entityRecord.getCanQuote());
                ps.setBoolean(12,entityRecord.getUseRecyclebin()==null ? false:entityRecord.getUseRecyclebin());
                ps.setBoolean(13, entityRecord.getDefineSet()==null ? false:entityRecord.getDefineSet());
                ps.setBoolean(14,entityRecord.getSetElement()==null ? false:entityRecord.getSetElement());
                ps.setBoolean(15, entityRecord.getNodisplay()==null ? false:entityRecord.getNodisplay());
            }
        });
        // 批量添加字段记录
        batchInsert(new ArrayList<FieldRecord>(fields));
    }

    /**
     * 批量注册实体默认字段信息(xiaogq)
     * @param objs
     */
    public void batchInsert(final List<FieldRecord> objs){
        StringBuffer sql = new StringBuffer("insert into sys_custom_field(id,name,entity_id,inchinese,sys_field,list_show,");
        sql.append(" filter_show,hidden_edit,hidden_show,nulldisable,read_only,default_value,length,precisions,position)");
        sql.append(" values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
        final int maxid = jdbcTemplate.queryForObject( " select max(id) from sys_custom_field ",Integer.class );
        jdbcTemplate.batchUpdate(sql.toString(), new BatchPreparedStatementSetter(){
            public int getBatchSize() {
                return objs.size();
            }
            public void setValues(PreparedStatement ps, int index) throws SQLException {
                FieldRecord fieldRecord = objs.get(index);
                String fieldName = fieldRecord.getName();
                ps.setInt(1, maxid + index + 1);
                ps.setString(2, fieldName);
                ps.setInt(3, fieldRecord.getEntityRecord().getTableCode());
                ps.setString(4, fieldRecord.getInchinese());
                ps.setBoolean(5, fieldRecord.isSysField());
                //ID和有效数据字段默认不在列表布局和过滤条件中显示，设置默认值：null
                boolean layoutHide = false;
                if(fieldName.equals("valid_data_bit") || fieldName.equals("id")){
                    layoutHide = true;
                }
                ps.setBoolean(6, layoutHide);
                ps.setBoolean(7, layoutHide);
                //ID、是否有效数据、密码字段默认不在编辑布局中显示，设置默认值：1
                boolean eidtBoo = false;
                if(fieldName.equals("id") || fieldName.equals("valid_data_bit") || fieldName.equals("password_varchar") || fieldRecord.isHiddenEdit()){
                    eidtBoo = true;
                }
                ps.setBoolean(8, eidtBoo || fieldRecord.isHiddenEdit() );
                //ID、是否有效数据、密码字段默认不在显示布局中显示，设置默认值：1
                boolean showBoo = false;
                if(fieldName.equals("id") || fieldName.equals("valid_data_bit") || fieldName.equals("password_varchar")){
                    showBoo = true;
                }
                ps.setBoolean(9, showBoo);
                ps.setBoolean(10, fieldRecord.isNulldisable());
                ps.setBoolean(11, fieldRecord.isReadOnly());
                ps.setString(12, fieldRecord.getDefaultValue() == null ? "" : fieldRecord.getDefaultValue());
                ps.setInt(13, fieldRecord.getLength());
                ps.setInt(14, fieldRecord.getPrecision());
                ps.setInt(15,index+1);
            }
        });
    }

    @Override
    public void rollbackDbException() {
        // 回滚创建的表
        jdbcTemplate.execute("drop table  if exists t_" + entityRecord.getTableCode());
    }
}
